package com.tsing.cedap.rest.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.tsing.cedap.rest.entity.SchoolEntity;
import com.tsing.cedap.rest.vo.reqVo.NameAndPhonePageReqVo;
import com.tsing.cedap.rest.vo.respVo.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 学校表 Mapper 接口
 * </p>
 *
 * @author TheTsing
 * @since 2022-01-26
 */
public interface SchoolMapper extends BaseMapper<SchoolEntity> {

    @Select("<script>" +
            "select s.*,u.name userName,u.phone userPhone\n" +
            "from cedap_school s\n" +
            "left join cedap_user u on s.user_id=u.id" +
            "<where>" +
            "<if test=\" reqVo.name !=null and reqVo.name !='' \">and s.name like concat('%',#{reqVo.name},'%')</if>" +
            "<if test=\" reqVo.phone !=null and reqVo.phone !='' \">and u.phone like concat('%',#{reqVo.phone},'%')</if>" +
            "</where>" +
            "</script>")
    List<SchoolRespVo> schoolPage(IPage<SchoolRespVo> page, @Param("reqVo") NameAndPhonePageReqVo reqVo);

    @Select("select count(co.id)\n" +
            "from cedap_school s\n" +
            "inner join cedap_campus c on s.id=c.school_id\n" +
            "inner join cedap_college co on c.id=co.campus_id\n" +
            "where s.id=#{id}")
    Long collegeCount(@Param("id") String id);

    @Select("select count(cs.id)\n" +
            "from cedap_school s\n" +
            "inner join cedap_campus c on s.id=c.school_id\n" +
            "inner join cedap_courier_station cs on c.id=cs.campus_id\n" +
            "where s.id=#{id}")
    Long courierStationCount(@Param("id") String id);

    @Select("select count(u.id)\n" +
            "from cedap_school s\n" +
            "inner join cedap_campus c on s.id=c.school_id\n" +
            "inner join cedap_college co on c.id=co.campus_id\n" +
            "inner join cedap_user u on co.id=u.college_id\n" +
            "where s.id=#{id}")
    Long userCount(@Param("id") String id);

    @Select("select count(u.id)\n" +
            "from cedap_campus c\n" +
            "inner join cedap_college co on c.id=co.campus_id\n" +
            "inner join cedap_user u on co.id=u.college_id\n" +
            "where c.id=#{id}")
    Long userCount2(@Param("id") String id);

    @Select("select count(u.id)\n" +
            "from cedap_school s\n" +
            "inner join cedap_campus c on s.id=c.school_id\n" +
            "inner join cedap_college co on c.id=co.campus_id\n" +
            "inner join cedap_user u on co.id=u.college_id\n" +
            "where s.id=#{id} and u.is_rider=2")
    Long riderCount(@Param("id") String id);

    @Select("select count(u.id)\n" +
            "from cedap_campus c\n" +
            "inner join cedap_college co on c.id=co.campus_id\n" +
            "inner join cedap_user u on co.id=u.college_id\n" +
            "where c.id=#{id} and u.is_rider=2")
    Long riderCount2(@Param("id") String id);

    @Select("<script>" +
            "select c.*,u.name userName,u.phone userPhone,s.name schoolName\n" +
            "from cedap_campus c\n" +
            "left join cedap_school s on c.school_id=s.id\n" +
            "left join cedap_user u on c.user_id=u.id" +
            "<where>" +
            "<if test=\" reqVo.name !=null and reqVo.name !='' \">and c.name like concat('%',#{reqVo.name},'%')</if>" +
            "<if test=\" reqVo.phone !=null and reqVo.phone !='' \">and u.phone like concat('%',#{reqVo.phone},'%')</if>" +
            "</where>" +
            "</script>")
    List<CampusRespVo> campusPage(IPage<CampusRespVo> page, @Param("reqVo") NameAndPhonePageReqVo reqVo);

    @Select("<script>" +
            "select c.*,u.name userName,u.phone userPhone,s.id schoolId,s.name schoolName,ca.name campusName\n" +
            "from cedap_college c\n" +
            "left join cedap_campus ca on c.campus_id=ca.id\n" +
            "left join cedap_school s on ca.school_id=s.id\n" +
            "left join cedap_user u on c.user_id=u.id" +
            "<where>" +
            "<if test=\" reqVo.name !=null and reqVo.name !='' \">and c.name like concat('%',#{reqVo.name},'%')</if>" +
            "<if test=\" reqVo.phone !=null and reqVo.phone !='' \">and u.phone like concat('%',#{reqVo.phone},'%')</if>" +
            "</where>" +
            "</script>")
    List<CollegeRespVo> collegePage(IPage<CollegeRespVo> page, @Param("reqVo") NameAndPhonePageReqVo reqVo);

    @Select("<script>" +
            "select c.*,u.name userName,u.phone userPhone,ca.name campusName\n" +
            "from cedap_courier_station c\n" +
            "left join cedap_campus ca on c.campus_id=ca.id\n" +
            "left join cedap_user u on c.user_id=u.id" +
            "<where>" +
            "<if test=\" reqVo.name !=null and reqVo.name !='' \">and c.name like concat('%',#{reqVo.name},'%')</if>" +
            "<if test=\" reqVo.phone !=null and reqVo.phone !='' \">and u.phone like concat('%',#{reqVo.phone},'%')</if>" +
            "</where>" +
            "</script>")
    List<StationRespVo> stationPage(IPage<StationRespVo> page, @Param("reqVo") NameAndPhonePageReqVo reqVo);

    @Select("select count(c.id)\n" +
            "from cedap_order c\n" +
            "inner join cedap_express_brand e on c.express_brand_id=e.id\n" +
            "inner join cedap_courier_station cs on e.courier_station_id=cs.id\n" +
            "where cs.id=#{id}")
    Long takePartCount(@Param("id") String id);

    @Select("<script>" +
            "select c.*,cs.id courierStationId,cs.name courierStationName\n" +
            "from cedap_express_brand c\n" +
            "left join cedap_courier_station cs on c.courier_station_id=cs.id" +
            "<where>" +
            "<if test=\" reqVo.name !=null and reqVo.name !='' \">and c.name like concat('%',#{reqVo.name},'%')</if>" +
            "</where>" +
            "</script>")
    List<BrandRespVo> brandPage(IPage<BrandRespVo> page, @Param("reqVo") NameAndPhonePageReqVo reqVo);

}
